﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Settings;
using System.Data;
using System.Dynamic;
using Utils;
using System.Data.SqlClient;

namespace PanasonicWarranty.Business
{
    public partial class pa_WarrantyDetail : BaseClass
    {

        public static pa_WarrantyDetail GetByID(int id)
        {
            using (DBWarrantyDataContext context = new DBWarrantyDataContext(AppSetting.ConnectionString))
            {
                return context.pa_WarrantyDetails.FirstOrDefault(p => p.WarrantyDetailID == id);
            }
        }

        public static DataTable GetAll(int? agencyID,
                                            string model, string product, string phone, DateTime fromDate, DateTime toDate)
        {
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@ModelCode", string.IsNullOrWhiteSpace(model)?(object)DBNull.Value:model),
                new SqlParameter("@ProductCode", string.IsNullOrWhiteSpace(product)?(object)DBNull.Value:product),
                new SqlParameter("@Phone", string.IsNullOrWhiteSpace(phone)?(object)DBNull.Value:phone.ToNormalPhoneNumber()),
                new SqlParameter("@AgencyID", agencyID??(object)DBNull.Value),
                new SqlParameter("@StartDate", fromDate),
                new SqlParameter("@EndDate", toDate)
            };
            return ExecuteReader("usp_WarranyDetail_GetAll", CommandType.StoredProcedure, param);
        }
        public static DataTable Paging(int currentPage, int pageSize, ref int totalRecord, int? agencyID,
                                            string model, string product, string phone, DateTime? fromDate, DateTime? toDate)
        {
            SqlParameter[] param = new SqlParameter[]{
                new SqlParameter("@CurrentPage", currentPage),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@TotalRecord",0),
                new SqlParameter("@ModelCode", string.IsNullOrWhiteSpace(model)?(object)DBNull.Value:model),
                new SqlParameter("@ProductCode", string.IsNullOrWhiteSpace(product)?(object)DBNull.Value:product),
                new SqlParameter("@Phone", string.IsNullOrWhiteSpace(phone)?(object)DBNull.Value:phone.ToNormalPhoneNumber()),
                new SqlParameter("@AgencyID", agencyID??(object)DBNull.Value),
                new SqlParameter("@StartDate", fromDate??(object)DBNull.Value),
                new SqlParameter("@EndDate", toDate??(object)DBNull.Value)
            };
            param[2].Direction = ParameterDirection.Output;
            DataTable retTable = ExecuteReader("usp_WarranyDetail_Pagging", CommandType.StoredProcedure, param);
            totalRecord = Convert.ToInt32(param[2].Value);
            return retTable;

        }

        public static DataTable PagingDetailAndReject(int currentPage, int pageSize, ref int totalRecord, int? agencyID,
                                            string model, string product, string phone, string status, DateTime? fromDate, DateTime? toDate)
        {
            SqlParameter[] param = new SqlParameter[]{
                new SqlParameter("@CurrentPage", currentPage),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@TotalRecord",0),
                new SqlParameter("@ModelCode", string.IsNullOrWhiteSpace(model)?(object)DBNull.Value:model),
                new SqlParameter("@ProductCode", string.IsNullOrWhiteSpace(product)?(object)DBNull.Value:product),
                new SqlParameter("@Phone", string.IsNullOrWhiteSpace(phone)?(object)DBNull.Value:phone.ToNormalPhoneNumber()),
                new SqlParameter("@AgencyID", agencyID??(object)DBNull.Value),
                new SqlParameter("@Status", status??(object)DBNull.Value),
                new SqlParameter("@StartDate", fromDate??(object)DBNull.Value),
                new SqlParameter("@EndDate", toDate??(object)DBNull.Value)
            };
            param[2].Direction = ParameterDirection.Output;
            DataTable retTable = ExecuteReader("usp_WarranyDetail_Pagging3", CommandType.StoredProcedure, param);
            totalRecord = Convert.ToInt32(param[2].Value);
            return retTable;

        }

        public static DataTable GetDetailAndReject(int? agencyID,
                                            string model, string product, string phone, string status, DateTime? fromDate, DateTime? toDate)
        {
            SqlParameter[] param = new SqlParameter[]{
                new SqlParameter("@ModelCode", string.IsNullOrWhiteSpace(model)?(object)DBNull.Value:model),
                new SqlParameter("@ProductCode", string.IsNullOrWhiteSpace(product)?(object)DBNull.Value:product),
                new SqlParameter("@Phone", string.IsNullOrWhiteSpace(phone)?(object)DBNull.Value:phone.ToNormalPhoneNumber()),
                new SqlParameter("@AgencyID", agencyID??(object)DBNull.Value),
                new SqlParameter("@Status", status??(object)DBNull.Value),
                new SqlParameter("@StartDate", fromDate??(object)DBNull.Value),
                new SqlParameter("@EndDate", toDate??(object)DBNull.Value)
            };
            DataTable retTable = ExecuteReader("usp_WarranyDetail_GetDetailAndReject", CommandType.StoredProcedure, param);
            return retTable;
        }

        public static object PagingByPager(int currentPage, int pageSize, int? agencyID,
                                            string model, string product, string phone, DateTime? startDate, DateTime? endDate)
        {
            return ExecuteReader("usp_WarranyDetail_Pagging2", CommandType.StoredProcedure,
                new SqlParameter("@CurrentPage", currentPage),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@ModelCode", string.IsNullOrWhiteSpace(model) ? (object)DBNull.Value : model),
                new SqlParameter("@ProductCode", string.IsNullOrWhiteSpace(product) ? (object)DBNull.Value : product),
                new SqlParameter("@Phone", string.IsNullOrWhiteSpace(phone) ? (object)DBNull.Value : phone.ToNormalPhoneNumber()),
                new SqlParameter("@AgencyID", agencyID ?? (object)DBNull.Value),
                new SqlParameter("@StartDate", startDate ?? (object)DBNull.Value),
                new SqlParameter("@EndDate", endDate ?? (object)DBNull.Value)
                );

        }

        public static DataTable Statistics(int? agencyID, string warrantyType, DateTime? startDate, DateTime? endDate)
        {
            return ExecuteReader("usp_WarranyDetail_Statistic", CommandType.StoredProcedure,
                new SqlParameter("@AgencyID", agencyID ?? (object)DBNull.Value),
                new SqlParameter("@WarrantyType", string.IsNullOrEmpty(warrantyType) ? (object)DBNull.Value : warrantyType),
                new SqlParameter("@StartDate", startDate ?? (object)DBNull.Value),
                new SqlParameter("@EndDate", endDate ?? (object)DBNull.Value)
            );
        }

        public static List<string> PhoneToMessagePaging(int currentPage, int pageSize, ref int totalRecord, DateTime? fromDate, DateTime? toDate)
        {
            using (var context = new DBWarrantyDataContext(AppSetting.ConnectionString))
            {
                var resutl = context.pa_WarrantyDetails.AsQueryable();    
                if (fromDate != null)
                {
                    resutl = resutl.Where(p => p.RegisterDate >= fromDate.Value);
                }
                if (toDate != null)
                {
                    resutl = resutl.Where(p => p.RegisterDate < toDate.Value);
                }

                var retValue = resutl.Select(p => p.CustomerPhone).Distinct();

                totalRecord = retValue.Count();
                return retValue.Skip(((currentPage - 1) < 0 ? 0 : (currentPage - 1)) * pageSize).Take(pageSize)
                       .ToList();
            }
        }

        public static List<string> PhoneToMessage(DateTime? fromDate, DateTime? toDate)
        {
            using (var context = new DBWarrantyDataContext(AppSetting.ConnectionString))
            {
                var query = context.pa_WarrantyDetails.AsQueryable();        
                if (fromDate != null)
                {
                    query = query.Where(p => p.RegisterDate >= fromDate.Value);
                }
                if (toDate != null)
                {
                    query = query.Where(p => p.RegisterDate < toDate.Value);
                }
                var result = query.Select(p => p.CustomerPhone).Distinct();
                return result.ToList();
            }
        }

        public static string GetCaption(string columnName)
        {
            switch (columnName)
            {
                case "Agency":
                case "AgencyName":
                    return "Đại lý";
                case "ModelCode":
                    return "Model";
                case "ProductCode":
                    return "Số máy";
                case "CustomerPhone":
                    return "Điện thoại KH";
                case "RegisterDate":
                    return "Ngày mua hàng";
                case "CreatedDate":
                    return "Ngày ĐK";
                case "ConfirmDate":
                    return "Ngày duyệt";
                case "WarrantyType":
                    return "Loại ĐK";
                case "RegisteredFrom":
                    return "ĐK qua";
                case "Description":
                    return "Thời hạn bảo hành";
                case "Status":
                    return "Trạng thái";
            }
            return "";
        }
    }
}
